import pymysql
import xlrd,time

# 连接数据库
def conn():
    return pymysql.Connect(user='local',                # 用户名
                           password='123456',           # 连接密码
                           port=3306,                   # 所用端口号
                           host='192.168.75.130',       # 所连IP地址
                           database='百度人员信息库')      # 所用数据库

# 单句执行
def DB_Cone(sql):
    con = conn()
    cur = con.cursor()

    cur.execute(sql)

    con.commit()
    cur.close()
    con.close()

# 批量操作
def DB_Cmany(sql,data):
    con = conn()
    cur = con.cursor()

    for i in data:
        print(i)
        try:
            cur.execute(sql, i)
        except EOFError as e:
            print(e)

    con.commit()
    cur.close()
    con.close()


# 读表：表单路径/内表名称
# 需要根据内容进行数据适配
def xls_read(fileway, sheetname):
    wb = xlrd.open_workbook(fileway)
    ws = wb.sheet_by_name(sheetname)

    data = []

    for i in range(1, ws.nrows):
        temp = []
        for j in range(ws.ncols):
            # 数据整理
            if (j==0)or(j==7):  # 整数
                d = int(ws.cell(i, j).value)
            elif (j==4):        # 字符串
                d = str(int(ws.cell(i, j).value))
            elif (j == 11):     # 浮点数
                d = float(ws.cell(i, j).value)
            else:
                d = ws.cell(i, j).value
            temp.append(d)
        data.append(temp)
    print(data)

    sql = "insert into 人员信息表 values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ,%s)"

    DB_Cmany(sql, data)


# 新建数据表 - 人员信息表
# DB_Cone("CREATE TABLE 人员信息表(" \
#           "序号 INT," \
#           "身份标识 CHAR(13)," \
#           "登录名 CHAR(20)," \
#           "真实姓名 CHAR(8)," \
#           "密码 CHAR(20)," \
#           "电话号码 CHAR(12)," \
#           "邮箱 CHAR(40)," \
#           "年龄 INT," \
#           "性别 CHAR(2)," \
#           "居住地址 CHAR(60)," \
#           "入职日期 DATETIME," \
#           "薪资 FLOAT(15)," \
#           "职责 CHAR(40)," \
#           "外包公司 CHAR(20)," \
#           "PRIMARY KEY(序号,身份标识)" \
#           ");")

# xls_read(r'5.baidu人员管理.xls', '人员管理')

'''>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>'''
# sql为语句框架，data为附加数据类型，f为额外查询格式
# 此函数返回第一条结果的累加值
def DB_Cc(sql, data):
    con = conn()
    cur = con.cursor()

    sum = 0
    for i in data:
        sqld = sql + "'"+str(i) + "'"
        print(f'\033[0;0;37m {sqld} \033[0m')
        cur.execute(sqld)
        sum += cur.fetchone()[0]

    con.commit()
    cur.close()
    con.close()

    return sum

# 改进方法,对于运行商分类，先读表后统计
def func_DBup():
    con = conn()
    cur = con.cursor()
    sql = "SELECT 电话号码 FROM 人员信息表"
    su = [0, 0, 0]

    cur.execute(sql)
    list = cur.fetchall()
    for i in list:
        if (int(i[0][:3]) in 移动) or (int(i[0][:4]) in 移动):
            su[0] += 1
        elif (int(i[0][:3]) in 联通) or (int(i[0][:4]) in 联通):
            su[1] += 1
        elif (int(i[0][:3]) in 电信) or (int(i[0][:4]) in 电信):
            su[2] += 1
        else:
            print(i, i[0], i[0][:3], type(i[0][:3]))

    con.commit()
    cur.close()
    con.close()
    return su

# 数据处理 /flag=0时仅加后位，其余情况前后都加
def strADD_list(data, s, flag):
    temp = []
    if flag == 0:
        for i in data:
            temp.append(str(i)+s)
        return temp
    else:
        for i in data:
            temp.append(s+str(i)+s)
        return temp

# 统计总人数
print('\n/////总人数统计')
sql = "SELECT COUNT(*) FROM 人员信息表 WHERE 真实姓名 LIKE "
data = ['%']
print(DB_Cc(sql,data))


# 统计三大运营商用户量
print('\n/////三大运行商用户统计')
sql = "SELECT COUNT(*) FROM 人员信息表 WHERE CONVERT(电话号码, CHAR) LIKE "
移动 = [134,135,136,137,138,139,147,150,151,152,157,158,159,178,182,183,184,187,188,1703,1705,1706]
联通 = [130,131,132,145,155,156,171,175,176,185,186,1704,1707,1708,1709]
电信 = [133,149,153,173,177,180,181,189,1700,1701,1702]

移动1 = strADD_list(移动, '%', 0)
联通1 = strADD_list(联通, '%', 0)
电信1 = strADD_list(电信, '%', 0)

t = time.time()
lis = func_DBup()
print('优化方式，获取表单数据后分类')
print("移动%d,\t联通%d,\t电信%d" % (lis[0],lis[1],lis[2]))
print('所用时间：%.2fs' % (time.time()-t))

t = time.time()
print('\n按照分类，对着数据库硬查')
print("移动%d,\t联通%d,\t电信%d" % (DB_Cc(sql, 移动1),DB_Cc(sql, 联通1),DB_Cc(sql, 电信1)))
print('所用时间：%.2fs' % (time.time()-t))

# 总公司男女人数
print('\n/////男女人数统计')
sql = "SELECT COUNT(*) FROM 人员信息表 WHERE 性别 = "
sex = ['男', '女']

print(DB_Cc(sql, sex[0]))
print(DB_Cc(sql, sex[1]))

# 超过45岁员工人数
print('\n/////大龄人数统计')
sql = "SELECT COUNT(*) FROM 人员信息表 WHERE 年龄 >  "
data = ['45']

print(DB_Cc(sql, data))

# 薪资高于8000元的高薪人员数量和薪资低于3000的底薪人员数量
print('\n/////高薪(>8000)和低薪(<3000)人员统计统计')
sql = "SELECT COUNT(*) FROM 人员信息表 WHERE 薪资 "
data = ['8000', '3000']

print(DB_Cc(sql+'>', data[0:1]))
print(DB_Cc(sql+'<', data[1:]))

# 统计去传媒公司的工作的人员数量
sql = "SELECT COUNT(*) FROM 人员信息表 WHERE 外包公司 LIKE "
data = ['%传媒%']

print(DB_Cc(sql, data[0:]))

# 统计一下可能在疫情高危地区的人数（高危地区：黑龙江，北京，福建，四川）
print('\n/////疫情高危地区人群统计')
sql = "SELECT COUNT(*) FROM 人员信息表 WHERE 居住地址 LIKE "
data = ['黑龙江', '北京', '福建', '四川']
data = strADD_list(data, '%', 1)

print(DB_Cc(sql, data))